package com.dognessnetwork.operation.ops.repository;
import java.util.List;

import javax.persistence.Table;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.dognessnetwork.operation.ops.domain.Messages;

/**
 * = MessagesRepository
 TODO Auto-generated class documentation
 *
 */
@Repository
@Table(name="d_content")
@Qualifier("messagesRepository")
public interface MessagesRepository extends CrudRepository<Messages, Long> {
	@Query(value = "select * from d_content where id=:id",nativeQuery = true)
	public Messages  findOne(@Param("id")Long id);
	
	/*
	@Query(value = "insert into d_content (content, customerIdCard, receiveTime, sendTime, sender, userIdCard) value(?,?,?,?,?,?)", nativeQuery = true)
    @Transactional
    public int createMessage(
    		@Param("content")String content,
    		@Param("customerIdCard") String customerIdCard,
    		@Param("receiveTime") String receiveTime,
    		@Param("sendTime") String sendTime,
    		@Param("sender") boolean sender,
    		@Param("userIdCard") String userIdCard);*/
	
	
	Page<Messages> findBySenderOrderBySendTimeAsc(boolean	sender,Pageable pageable);
	
	Page<Messages> findByUserIdCardOrderBySendTimeAsc(String	userIdCard,Pageable pageable);

	/**
	 * 查询有关用户消息总条数
	 * @param userIdCard
	 * @return
	 */
    @Query(value="SELECT COUNT(*) FROM d_content WHERE user_id_card = :userIdCard", nativeQuery = true)
	public	int queryCountMessages(@Param("userIdCard")String userIdCard);
	
    @Query(value="SELECT MAX(id) FROM d_content WHERE sender=0 AND user_id_card = :userIdCard", nativeQuery = true)
    public int queryMaxMessageId(@Param("userIdCard")String userIdCard);
    /**
     * 轮询获取回复
     * @param id
     * @param userIdCard
     * @return
     */
    @Query(value="SELECT * FROM d_content WHERE id > :id AND user_id_card = :userIdCard", nativeQuery = true)
    public List<Messages> queryAnswer(@Param("id") Long id,@Param("userIdCard")String userIdCard);
    
	@Modifying
    @Transactional
    @Query("update Messages d set d.receiveTime = :receiveTime where d.id=:id")
    public int queryMessagesById(@Param("id") Long id,@Param("receiveTime") String receiveTime);
    
    /**
     * 获取未回复的用户
     * @return
     */
    @Modifying
    @Query(value = "select s.sender,s.user_id_card from(select user_id_card,sender from d_content order by send_time DESC) as s  group by user_id_card"
    , nativeQuery = true)
    public	List<Object[]> queryUserIdCard();
    
    @Modifying
    @Query(value="SELECT d.user_id_card FROM ("+
    		"SELECT s.sender,s.user_id_card FROM (SELECT user_id_card,sender FROM d_content ORDER BY send_time DESC)  AS s  GROUP BY user_id_card"+
    		") AS d WHERE d.sender=0",nativeQuery = true)
    public	List<Object> queryUserlist();
    
    /**
     * 获取用户问题
     * @param userIdCard
     * @return
     */
    @Modifying
    @Query(value="SELECT sender,send_time,content FROM d_content WHERE user_id_card=:userIdCard  ORDER BY send_time ASC", nativeQuery = true)
    public	List<Object[]>	queryMessagesByUserIdCard(@Param("userIdCard") String	userIdCard);
    
    @Query(value="SELECT COUNT(id) FROM d_content WHERE user_id_card =:userIdCard AND id > (SELECT MAX(id) FROM d_content WHERE user_id_card = =:userIdCard AND customer_id_card != '')",nativeQuery = true)
    public int queryNumberNotReply(@Param("userIdCard") String	userIdCard);
}
